/*
 * Created on 2006-3-4
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.hitrust.gs.business.report;

import java.sql.*;
import java.util.ArrayList;

import com.hitrust.gs.dao.GSOrderDAO;
import com.hitrust.gs.dao.GSOrderDetailDAO;
import com.hitrust.gs.vo.GSOrderVO;
import com.hitrust.gs.vo.report.OrderStockStatVO;
import com.hitrust.gs.vo.report.*;
import com.hitrust.gs.form.report.*;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.service.GSBaseManager;
import com.hitrust.gs.util.exception.*;
import com.hitrust.gs.bean.GSOperatorBean;
import com.hitrust.gs.lib.GSBaseConstants;

/**
 * @author simonyi
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class GSOrderQuery {

    private GSOrderDAO dao = new GSOrderDAO();

    public ArrayList selectNaturalOrder(GSNaturalOrderReportQueryForm aForm) throws
            GSException {

        String orderIdMin = aForm.getOrderidmin();
        String orderIdMax = aForm.getOrderidmax();
        String orderStatus = aForm.getOrderstatus();
        String manufactureName = aForm.getManfuacturename();
        String providerName = aForm.getProvidername();
        String deptId = aForm.getDeptid();
        String orderTotalPriceMin = aForm.getOrdertotalpricemin();
        String orderTotalPriceMax = aForm.getOrdertotalpricemax();
        String createDateMin = aForm.getCreatedatemin();
        String createDateMax = aForm.getCreatedatemax();
        String detailStatus = aForm.getDetailstatus();

        String condition = "";
        if (orderIdMin != null && orderIdMax != null &&
            orderIdMin.length() != 0 && orderIdMax.length() != 0) {
            condition += " and B.GS_ORDER_TEMPID > '" + orderIdMin +
                    "' and B.GS_ORDER_TEMPID < '" + orderIdMax + "' ";
        }
        if (orderStatus != null && orderStatus.length() != 0) {
            condition += " and A.GS_ORDER_STATUS = '" + orderStatus + "' ";
        }
        if (manufactureName != null && manufactureName.length() != 0) {
            condition += " and A.GS_ORDER_STATUS like '%" + manufactureName +
                    "%' ";
        }
        if (providerName != null && providerName.length() != 0) {
            condition += " and D.GS_PROVIDER_NAME like '%" + providerName +
                    "%'";
        }
        if (deptId != null && deptId.length() != 0) {
            condition += " and B.GS_WORKCORP_ID = '" + deptId + "' ";
        }
        if (orderTotalPriceMin != null && orderTotalPriceMax != null &&
            orderTotalPriceMin.length() != 0 &&
            orderTotalPriceMax.length() != 0) {
            condition += " and A.GS_ORDER_SUM >= '" + orderTotalPriceMin +
                    "' and A.GS_ORDER_SUM <= '" + orderTotalPriceMax + "' ";
        }
        if (createDateMin != null && createDateMax != null &&
            createDateMin.length() != 0 && createDateMax.length() != 0) {
            condition += " and A.GS_ORDER_CREATE_DATE >= '" + createDateMin +
                    "' and A.GS_ORDER_CREATE_DATE <= '" + createDateMax + "' ";
        }
        if (detailStatus != null && detailStatus.length() != 0) {
            condition += " and B.GS_ORDER_DETAIL_STATUS = '" + detailStatus +
                    "' ";
        }

        String sql = "select DISTINCT T1.GS_ORDER_TEMPID as P1, T1.GS_ORDER_STATUS as P2, T1.GS_CONCENTRICITY_ID as P3,  " +
                     " T1.GS_MANUFACTURE_ID as P4, T1.GS_MANUFACTURE_NAME as P5,  T1.GS_MANUFACTURE_UNITPRICE as P6," +
                     " T1.GS_ORDER_COUNT as P7, (T1.GS_ORDER_COUNT * T1.GS_MANUFACTURE_UNITPRICE) as P8, " +
                     " T1.GS_ORDER_DEPT_ID as P9,T1.GS_ORDER_DETAIL_STATUS as P10,T2.GS_PROVIDER_NAME as P11," +
                     " T1.GS_QUANT_UNIT as P12,T1.GS_NOTES as P99 " +
                     " from ( select A.GS_ORDER_TEMPID , A.GS_ORDER_STATUS , B.GS_CONCENTRICITY_ID ,  " +
                     " B.GS_MANUFACTURE_ID , C.GS_MANUFACTURE_NAME ,  C.GS_MANUFACTURE_UNITPRICE ," +
                     " B.GS_ORDER_COUNT , B.GS_ORDER_DEPT_ID ,B.GS_ORDER_DETAIL_STATUS," +
                     " C.GS_QUANT_UNIT  ,B.GS_PROVIDER_ID ,A.GS_NOTES  " +
                     " from gs_order A,gs_order_detail B,gs_manufacture C" +
                     " where A.GS_ORDER_CATEGORY=0 and a.gs_order_tempid = b.gs_order_tempid and " +
                     //" (" +
                     " b.gs_manufacture_id = c.gs_manufacture_id " +
                     //" or b.gs_concentricity_id = c.gs_concentricity_id" +
                     //" ) " +
                     condition + ") T1 left join GS_PROVIDER T2 " +
                     " on T1.GS_PROVIDER_ID = T2.GS_PROVIDER_ID" +
                     " order by T1.gs_order_tempid";

        GSNaturalOrderVO vo = new GSNaturalOrderVO();
        ArrayList temp = null;
        try {
            temp = dao.executeQuery(vo, sql, null);
        } catch (Exception sqle) {
            throw new GSException(GSException.E00001,
                                  GSException.E00001_MSG);
        }
        ArrayList ary = new ArrayList();
        String tOrderId = "";

        GSOrderVO tGSOrderVO = null;
        for (int i = 0; i < temp.size(); i++) {
            GSNaturalOrderVO ovo = (GSNaturalOrderVO) temp.get(i);
            //判断新取得的VO对象中的订单编号是否与前一对象中订单编号相同
            if (tOrderId.equals(ovo.orderId)) {
                //如果相同，直接在当前订单对象中加载明细
                String tStr[] = new String[10];
                tStr[0] = ovo.concentricityId;
                tStr[1] = ovo.manufactureId;
                tStr[2] = ovo.manufactureName;
                tStr[3] = ovo.manufactureUnitPrice;
                tStr[4] = ovo.orderCount;
                tStr[5] = ovo.totalPrice;
                tStr[6] = ovo.deptId;
                tStr[7] = ovo.detailStatus;
                tStr[8] = ovo.providerName;
                tStr[9] = ovo.quantUnit;

                tGSOrderVO.tList.add(tStr);
            } else {
                //如果不同，构造一个新订单，在该订单对象中加载明细并把订单存入list
                tGSOrderVO = new GSOrderVO();
                tGSOrderVO.tList = new ArrayList();
                tGSOrderVO.orderid = ovo.orderId;
                tGSOrderVO.orderStatus = ovo.orderStatus;

                String tStr[] = new String[10];
                tStr[0] = ovo.concentricityId;
                tStr[1] = ovo.manufactureId;
                tStr[2] = ovo.manufactureName;
                tStr[3] = ovo.manufactureUnitPrice;
                tStr[4] = ovo.orderCount;
                tStr[5] = ovo.totalPrice;
                tStr[6] = ovo.deptId;
                tStr[7] = ovo.detailStatus;
                tStr[8] = ovo.providerName;
                tStr[9] = ovo.quantUnit;

                tGSOrderVO.tList.add(tStr);
                tOrderId = ovo.orderId;
                ary.add(tGSOrderVO);
            }

        }
        return ary;
    }

    public ArrayList selectLashUpOrder(GSNaturalOrderReportQueryForm aForm) throws
            GSException {

        String orderIdMin = aForm.getOrderidmin();
        String orderIdMax = aForm.getOrderidmax();
        String orderStatus = aForm.getOrderstatus();
        String manufactureName = aForm.getManfuacturename();
        String providerName = aForm.getProvidername();
        String deptId = aForm.getDeptid();
        String orderTotalPriceMin = aForm.getOrdertotalpricemin();
        String orderTotalPriceMax = aForm.getOrdertotalpricemax();
        String createDateMin = aForm.getCreatedatemin();
        String createDateMax = aForm.getCreatedatemax();
        String detailStatus = aForm.getDetailstatus();

        String condition = "";
        if (orderIdMin != null && orderIdMax != null &&
            orderIdMin.length() != 0 && orderIdMax.length() != 0) {
            condition += " and B.GS_ORDER_TEMPID > '" + orderIdMin +
                    "' and B.GS_ORDER_TEMPID < '" + orderIdMax + "' ";
        }
        if (orderStatus != null && orderStatus.length() != 0) {
            condition += " and A.GS_ORDER_STATUS = '" + orderStatus + "' ";
        }
        if (manufactureName != null && manufactureName.length() != 0) {
            condition += " and A.GS_ORDER_STATUS like '%" + manufactureName +
                    "%' ";
        }
        if (providerName != null && providerName.length() != 0) {
            condition += " and D.GS_PROVIDER_NAME like '%" + providerName +
                    "%'";
        }
        if (deptId != null && deptId.length() != 0) {
            condition += " and B.GS_WORKCORP_ID = '" + deptId + "' ";
        }
        if (orderTotalPriceMin != null && orderTotalPriceMax != null &&
            orderTotalPriceMin.length() != 0 &&
            orderTotalPriceMax.length() != 0) {
            condition += " and A.GS_ORDER_SUM >= '" + orderTotalPriceMin +
                    "' and A.GS_ORDER_SUM <= '" + orderTotalPriceMax + "' ";
        }
        if (createDateMin != null && createDateMax != null &&
            createDateMin.length() != 0 && createDateMax.length() != 0) {
            condition += " and A.GS_ORDER_CREATE_DATE >= '" + createDateMin +
                    "' and A.GS_ORDER_CREATE_DATE <= '" + createDateMax + "' ";
        }
        if (detailStatus != null && detailStatus.length() != 0) {
            condition += " and B.GS_ORDER_DETAIL_STATUS = '" + detailStatus +
                    "' ";
        }

        String sql = "select DISTINCT T1.GS_ORDER_TEMPID as P1, T1.GS_ORDER_STATUS as P2, T1.GS_CONCENTRICITY_ID as P3,  " +
                     " T1.GS_MANUFACTURE_ID as P4, T1.GS_MANUFACTURE_NAME as P5,  T1.GS_MANUFACTURE_UNITPRICE as P6," +
                     " T1.GS_ORDER_COUNT as P7, (T1.GS_ORDER_COUNT * T1.GS_MANUFACTURE_UNITPRICE) as P8, " +
                     " T1.GS_ORDER_DEPT_ID as P9,T1.GS_ORDER_DETAIL_STATUS as P10,T2.GS_PROVIDER_NAME as P11," +
                     " T1.GS_QUANT_UNIT as P12,T1.GS_NOTES as P99  " +
                     " from ( select A.GS_ORDER_TEMPID , A.GS_ORDER_STATUS , B.GS_CONCENTRICITY_ID ,  " +
                     " B.GS_MANUFACTURE_ID , C.GS_MANUFACTURE_NAME ,  C.GS_MANUFACTURE_UNITPRICE ," +
                     " B.GS_ORDER_COUNT , B.GS_ORDER_DEPT_ID ,B.GS_ORDER_DETAIL_STATUS," +
                     " C.GS_QUANT_UNIT  ,B.GS_PROVIDER_ID ,A.GS_NOTES  " +
                     " from gs_order A,gs_order_detail B,gs_manufacture C" +
                     " where A.GS_ORDER_CATEGORY=1 and a.gs_order_tempid = b.gs_order_tempid and " +
                     //" (" +
                     " b.gs_manufacture_id = c.gs_manufacture_id " +
                     //" or b.gs_concentricity_id = c.gs_concentricity_id" +
                     //" ) " +
                     condition + ") T1 left join GS_PROVIDER T2 " +
                     " on T1.GS_PROVIDER_ID = T2.GS_PROVIDER_ID" +
                     " order by T1.gs_order_tempid";

        GSNaturalOrderVO vo = new GSNaturalOrderVO();
        ArrayList temp = null;
        try {
            temp = dao.executeQuery(vo, sql, null);
        } catch (Exception sqle) {
            throw new GSException(GSException.E00001,
                                  GSException.E00001_MSG);
        }
        ArrayList ary = new ArrayList();
        String tOrderId = "";

        GSOrderVO tGSOrderVO = null;
        for (int i = 0; i < temp.size(); i++) {
            GSNaturalOrderVO ovo = (GSNaturalOrderVO) temp.get(i);
            //判断新取得的VO对象中的订单编号是否与前一对象中订单编号相同
            if (tOrderId.equals(ovo.orderId)) {
                //如果相同，直接在当前订单对象中加载明细
                String tStr[] = new String[10];
                tStr[0] = ovo.concentricityId;
                tStr[1] = ovo.manufactureId;
                tStr[2] = ovo.manufactureName;
                tStr[3] = ovo.manufactureUnitPrice;
                tStr[4] = ovo.orderCount;
                tStr[5] = ovo.totalPrice;
                tStr[6] = ovo.deptId;
                tStr[7] = ovo.detailStatus;
                tStr[8] = ovo.providerName;
                tStr[9] = ovo.quantUnit;

                tGSOrderVO.tList.add(tStr);
            } else {
                //如果不同，构造一个新订单，在该订单对象中加载明细并把订单存入list
                tGSOrderVO = new GSOrderVO();
                tGSOrderVO.tList = new ArrayList();
                tGSOrderVO.orderid = ovo.orderId;
                tGSOrderVO.orderStatus = ovo.orderStatus;

                String tStr[] = new String[10];
                tStr[0] = ovo.concentricityId;
                tStr[1] = ovo.manufactureId;
                tStr[2] = ovo.manufactureName;
                tStr[3] = ovo.manufactureUnitPrice;
                tStr[4] = ovo.orderCount;
                tStr[5] = ovo.totalPrice;
                tStr[6] = ovo.deptId;
                tStr[7] = ovo.detailStatus;
                tStr[8] = ovo.providerName;
                tStr[9] = ovo.quantUnit;

                tGSOrderVO.tList.add(tStr);
                tOrderId = ovo.orderId;
                ary.add(tGSOrderVO);
            }

        }
        return ary;
    }

    public ArrayList selectOrderPlanTrace(GSOrderPlanTraceReportQueryForm aForm) throws
            GSException {

        String orderIdMin = aForm.getOrderidmin();
        String orderIdMax = aForm.getOrderidmax();
        String orderStatus = aForm.getOrderstatus();
        String deptId = aForm.getDeptid();
        String detailStatus = aForm.getDetailstatus();
        String orderCategory = aForm.getOrdercategory();

        String condition = "";
        if (orderIdMin != null && orderIdMax != null &&
            orderIdMin.length() != 0 && orderIdMax.length() != 0) {
            condition += " and B.GS_ORDER_TEMPID > '" + orderIdMin +
                    "' and B.GS_ORDER_TEMPID < '" + orderIdMax + "' ";
        }
        if (orderStatus != null && orderStatus.length() != 0) {
            condition += " and A.GS_ORDER_STATUS = '" + orderStatus + "' ";
        }
        if (orderCategory != null && orderCategory.length() != 0) {
            condition += " and A.GS_ORDER_CATEGORY = '" + orderCategory + "' ";
        }
        if (deptId != null && deptId.length() != 0) {
            condition += " and B.GS_WORKCORP_ID = '" + deptId + "' ";
        }
        if (detailStatus != null && detailStatus.length() != 0) {
            condition += " and B.GS_ORDER_DETAIL_STATUS = '" + detailStatus +
                    "' ";
        }

        String sql = "select DISTINCT T1.GS_ORDER_TEMPID as P1, T1.GS_ORDER_STATUS as P2, T1.GS_CONCENTRICITY_ID as P3,  " +
                     " T1.GS_MANUFACTURE_ID as P4, T1.GS_MANUFACTURE_NAME as P5,  T1.GS_MANUFACTURE_UNITPRICE as P6," +
                     " T1.GS_ORDER_COUNT as P7, (T1.GS_ORDER_COUNT * T1.GS_MANUFACTURE_UNITPRICE) as P8, " +
                     " T1.GS_ORDER_DEPT_ID as P9,T1.GS_ORDER_DETAIL_STATUS as P10,T2.GS_PROVIDER_NAME as P11," +
                     " T1.GS_QUANT_UNIT as P12,T1.GS_NOTES as P99 " +
                     " from ( select A.GS_ORDER_TEMPID , A.GS_ORDER_STATUS , B.GS_CONCENTRICITY_ID ,  " +
                     " B.GS_MANUFACTURE_ID , C.GS_MANUFACTURE_NAME ,  C.GS_MANUFACTURE_UNITPRICE ," +
                     " B.GS_ORDER_COUNT , B.GS_ORDER_DEPT_ID ,B.GS_ORDER_DETAIL_STATUS," +
                     " C.GS_QUANT_UNIT  ,B.GS_PROVIDER_ID ,A.GS_NOTES " +
                     " from gs_order A,gs_order_detail B,gs_manufacture C" +
                     " where A.GS_ORDER_CATEGORY=0 and a.gs_order_tempid = b.gs_order_tempid and " +
                     " b.gs_manufacture_id = c.gs_manufacture_id " +
                     condition + ") T1 left join GS_PROVIDER T2 " +
                     " on T1.GS_PROVIDER_ID = T2.GS_PROVIDER_ID" +
                     " order by T1.gs_order_tempid";

        GSNaturalOrderVO vo = new GSNaturalOrderVO();
        ArrayList temp = null;
        try {
            temp = dao.executeQuery(vo, sql, null);
        } catch (Exception sqle) {
            throw new GSException(GSException.E00001,
                                  GSException.E00001_MSG);
        }
        ArrayList ary = new ArrayList();
        String tOrderId = "";

        GSOrderVO tGSOrderVO = null;
        for (int i = 0; i < temp.size(); i++) {
            GSNaturalOrderVO ovo = (GSNaturalOrderVO) temp.get(i);
            //判断新取得的VO对象中的订单编号是否与前一对象中订单编号相同
            if (tOrderId.equals(ovo.orderId)) {
                //如果相同，直接在当前订单对象中加载明细
                String tStr[] = new String[10];
                tStr[0] = ovo.concentricityId;
                tStr[1] = ovo.manufactureId;
                tStr[2] = ovo.manufactureName;
                tStr[3] = ovo.manufactureUnitPrice;
                tStr[4] = ovo.orderCount;
                tStr[5] = ovo.totalPrice;
                tStr[6] = ovo.deptId;
                tStr[7] = ovo.detailStatus;
                tStr[8] = ovo.providerName;
                tStr[9] = ovo.quantUnit;

                tGSOrderVO.tList.add(tStr);
            } else {
                //如果不同，构造一个新订单，在该订单对象中加载明细并把订单存入list
                tGSOrderVO = new GSOrderVO();
                tGSOrderVO.tList = new ArrayList();
                tGSOrderVO.orderid = ovo.orderId;
                tGSOrderVO.orderStatus = ovo.orderStatus;
                tGSOrderVO.notes = ovo.notes;

                String tStr[] = new String[10];
                tStr[0] = ovo.concentricityId;
                tStr[1] = ovo.manufactureId;
                tStr[2] = ovo.manufactureName;
                tStr[3] = ovo.manufactureUnitPrice;
                tStr[4] = ovo.orderCount;
                tStr[5] = ovo.totalPrice;
                tStr[6] = ovo.deptId;
                tStr[7] = ovo.detailStatus;
                tStr[8] = ovo.providerName;
                tStr[9] = ovo.quantUnit;

                tGSOrderVO.tList.add(tStr);
                tOrderId = ovo.orderId;
                ary.add(tGSOrderVO);
            }

        }
        return ary;
    }


    /**
     * 查询一个订单的所有明细，用于订单录入
     * @param orderId
     * @return
     * @throws GSException
     */
    public ArrayList getOrderDetail(String orderId) throws GSException {
        ArrayList tList = new ArrayList();
        GSOrderDetailDAO tGSOrderDetailDAO = null;
        try {
            tGSOrderDetailDAO = new GSOrderDetailDAO();
            Connection conn = tGSOrderDetailDAO.getConnetion();

            String sql =
                    "select DISTINCT T1.GS_ORDER_TEMPID as P1, T1.GS_CONCENTRICITY_ID as P2,  " +
                    " T1.GS_MANUFACTURE_ID as P3, T1.GS_MANUFACTURE_NAME as P4,  T1.GS_MANUFACTURE_UNITPRICE as P5," +
                    " T1.GS_ORDER_COUNT as P6, (T1.GS_ORDER_COUNT * T1.GS_MANUFACTURE_UNITPRICE) as P7, " +
                    " T1.GS_ORG_NAME as P8, T1.GS_DELIVER_GOODS_ADDR as P9, " +
                    " T1.GS_DELIVER_GOODS_DATE as P10, T1.GS_DELIVER_GOODS_TIME as P11, " +
                    " T1.GS_ORDER_CONTACT_NAME as P12, T1.GS_ORDER_CONTACT_PHONE as P13, " +
                    " T1.GS_DELIVER_MODE as P14, T1.GS_BALANCE_MODE as P15, " +
                    " T1.GS_ORDER_DETAIL_STATUS as P16,T1.GS_QUANT_UNIT as P17," +
                    " T2.GS_PROVIDER_NAME as P18 " +
                    " from ( select B.GS_ORDER_TEMPID , B.GS_CONCENTRICITY_ID ,  " +
                    " B.GS_MANUFACTURE_ID , C.GS_MANUFACTURE_NAME ,  C.GS_MANUFACTURE_UNITPRICE ," +
                    " B.GS_ORDER_COUNT , D.GS_ORG_NAME ,B.GS_DELIVER_GOODS_ADDR ,B.GS_DELIVER_GOODS_DATE ," +
                    " B.GS_DELIVER_GOODS_TIME ,B.GS_ORDER_CONTACT_NAME, B.GS_ORDER_CONTACT_PHONE," +
                    " B.GS_DELIVER_MODE, B.GS_BALANCE_MODE, B.GS_ORDER_DETAIL_STATUS ," +
                    " C.GS_QUANT_UNIT  ,B.GS_PROVIDER_ID " +
                    " from gs_order_detail B,gs_manufacture C, gs_organization D " +
                    " where B.GS_ORDER_TEMPID = '" + orderId +
                    "' and D.gs_org_id = b.gs_order_dept_id and " +
                    " b.gs_manufacture_id = c.gs_manufacture_id )" +
                    " T1 left join GS_PROVIDER T2 " +
                    " on T1.GS_PROVIDER_ID = T2.GS_PROVIDER_ID";

            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            if (rs != null) {
                while (rs.next()) {
                    String[] tmp = new String[15];
                    tmp[0] = rs.getString("P2");
                    tmp[1] = rs.getString("P3");
                    tmp[2] = rs.getString("P4");
                    tmp[3] = rs.getString("P5");
                    tmp[4] = rs.getString("P6");
                    tmp[5] = rs.getString("P7");
                    tmp[6] = rs.getString("P8");
                    tmp[7] = rs.getString("P9");
                    tmp[8] = rs.getString("P10") + " " +
                             GSBaseManager.
                             getDeliverGoodsTime(rs.getString("P11"));
                    tmp[9] = rs.getString("P12") == null ? "" :
                             rs.getString("P12");
                    tmp[10] = rs.getString("P13") == null ? "" :
                              rs.getString("P13");
                    tmp[11] = rs.getString("P14") == null ? "" :
                              rs.getString("P14");
                    tmp[12] = rs.getString("P15") == null ? "" :
                              rs.getString("P15");
                    tmp[13] = rs.getString("P16") == null ? "" :
                              rs.getString("P16");
                    tmp[14] = rs.getString("P18") == null ? "" :
                              rs.getString("P18");

                    tList.add(tmp);
                }
                rs.close();
            }
            pstmt.close();

        } catch (Exception e) {
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E10042,
                                  GSException.E10042_MSG);
        } finally {
            tGSOrderDetailDAO.close();
        }
        return tList;

    }

    public ArrayList selectStockStatOrder(GSOrderStockStatReportQueryForm aForm) throws
            GSException {

        String sql = getStockStatOrderSQL(aForm);
        System.out.println("query:" + sql);
        OrderStockStatVO vo = new OrderStockStatVO();
        ArrayList temp = null;
        try {
            temp = dao.executeQuery(vo, sql, null);
        } catch (Exception sqle) {
            throw new GSException(GSException.E00001,
                                  GSException.E00001_MSG);
        }        
        return temp;
    }

    private String getStockStatOrderSQL(GSOrderStockStatReportQueryForm aForm) {
        GSOperatorBean tGSOperatorBean = (GSOperatorBean)aForm.getSession().getAttribute(GSBaseConstants.LOGIN);

        String order_tempid = aForm.getOrderid();
        String manufacture_name = aForm.getManufacturename();
        StringBuffer tCondition1 = new StringBuffer();
        if (order_tempid != null && order_tempid.length() > 0) {
            tCondition1.append(" And ordd.gs_order_tempid = '" + order_tempid +
                               "' ");
        }
        if (manufacture_name != null && manufacture_name.length() > 0) {
            tCondition1.append(" And man.GS_MANUFACTURE_NAME = '" +
                               manufacture_name + "' ");
        }

        StringBuffer tCondition2 = new StringBuffer();
        String order_create_date_min = aForm.getCreatedatemin();
        String order_create_date_max = aForm.getCreatedatemax();
        String order_category = aForm.getOrdercategory();
        String workcorp_id = aForm.getWorkcorpid();
        if (order_create_date_min != null && order_create_date_min.length() > 0) {
            tCondition2.append("  And odr.GS_ORDER_CREATE_DATE >='" +
                               order_create_date_min + "' ");
        }
        if (order_create_date_max != null && order_create_date_max.length() > 0) {
            tCondition2.append(" And odr.GS_ORDER_CREATE_DATE <='" +
                               order_create_date_max + "' ");
        }
        if (order_category != null && order_category.length() > 0) {
            tCondition2.append(" And odr.GS_ORDER_CATEGORY = '" +
                               order_category + "' ");
        }
        if (workcorp_id != null && workcorp_id.length() > 0) {
            tCondition2.append(" And odr.gs_workcorp_id = '" + workcorp_id +
                               "' ");
        }

        StringBuffer sql = new StringBuffer(
                "Select ordd.gs_manufacture_id As p1,man.GS_MANUFACTURE_NAME As p2, ");
        sql.append(
                " Sum(ordd.GS_ORDER_COUNT) As p3,Sum(ordd.GS_DELIVER_GOODS_COUNTS) As p4, ");
        sql.append(
                " Sum(ordd.GS_SIGNIN_COUNT) As p5,pro.GS_PROVIDER_NAME As p6 ");
        sql.append(
                " from gs_order_detail ordd,gs_manufacture man, gs_provider pro ");
        sql.append(" where ordd.gs_manufacture_id = man.gs_manufacture_id ");
        sql.append(" And ordd.GS_PROVIDER_ID = pro.GS_PROVIDER_ID ");
        sql.append(tCondition1);
        sql.append(" And ordd.gs_order_tempid In (Select odr.gs_order_tempid From gs_operator opr , gs_order odr ");
        String org_id = tGSOperatorBean.getGs_oper_org_id();
        if(!org_id.substring(0,2).equals("00") && !org_id.substring(2,4).equals("00")) { //运营公司所属部门及修理厂
            sql.append(
                    " Where opr.gs_oper_org_id = odr.gs_workcorp_id And opr.Gs_Oper_Org_Id = '" + org_id + "' ");
        } else if(!org_id.substring(0,2).equals("00") && org_id.substring(2,4).equals("00") ){ //运营公司
            sql.append(
                    " Where opr.gs_oper_org_id = odr.gs_workcorp_id And opr.Gs_Oper_Org_Id like '" + org_id.substring(0,2) + "%' ");
        } else if(org_id.substring(0,2).equals("00") && !org_id.substring(2,4).equals("00")) { //巴士物资所属部门
            sql.append(
                    " Where opr.gs_oper_org_id = odr.gs_workcorp_id And opr.Gs_Oper_Org_Id = '" + org_id + "' ");
        } else { //巴士物资
            sql.append(
                    " Where opr.gs_oper_org_id = odr.gs_workcorp_id And opr.Gs_Oper_Org_Id like '00%' ");
        }
        sql.append(tCondition2);
        sql.append(" ) ");
        sql.append(
                " Group By ordd.gs_manufacture_id,man.GS_MANUFACTURE_NAME,pro.GS_PROVIDER_NAME ");

        return sql.toString();
    }


}
